﻿#define local

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Text.RegularExpressions;
using System.Text;
using System.Web.SessionState;

using PL.Models;
namespace PL
{    
    /// <summary>
    /// Summary description for General
    /// </summary>
    public class General
    {
        #region Fields

        private static General instance;

        #endregion

        #region Connections

        public string EspacoVipConnectionString
        {
            get { return "Server=mysql.medapps.com.br;Database=medapps04;Uid=medapps04;Pwd=painel153;"; }
        }

        /// <summary>
        /// Gets the connection string of the SAC db.
        /// </summary>
        public string SACConnectionString
        {
#if local
            get { return "Server=srv.soitic.com;Database=SAC;Uid=sa;Pwd=medsys2o12;"; }
#else
            get { return "Server=200.199.34.58,1999;Database=SAC;Uid=sa;Pwd=medsys2o12;"; }
#endif
            //get { return "Server=187.49.197.78,1999;Database=SAC;Uid=sa;Pwd=medsys2o12;"; }
            //get { return "Server=192.168.0.3,1433;Database=CONPARE;Uid=sa;Pwd=medsys2o12;"; }
            //get { return "Server=187.49.197.78,1999;Database=CONPARE;Uid=sa;Pwd=medsys2o12;"; }
            //get { return "Server=srv\\SAC;Database=CONPARE;Uid=sa;Pwd=medsys2o12;"; }
            //get { return "Server=187.49.197.78,1999;Database=SAC;Uid=sa;Pwd=medsys2o12;"; }
            //get { return "Server=SERVER\\SAC,1433;Database=SAC;Uid=sa;Pwd=medsys2o12;"; }
        }

        /// <summary>
        /// Gets the connection string of the SAC db.
        /// </summary>
        public string ConpareConnectionString
        {
#if local
            get { return "Server=srv.soitic.com;Database=CONPARE;Uid=sa;Pwd=medsys2o12;"; }
#else
            get { return "Server=200.199.34.58,1999;Database=CONPARE;Uid=sa;Pwd=medsys2o12;"; }
#endif
            //get { return "Server=187.49.197.78,1999;Database=CONPARE;Uid=sa;Pwd=medsys2o12;"; }
            //get { return "Server=192.168.0.3,1433;Database=CONPARE;Uid=sa;Pwd=medsys2o12;"; }
        }

        /// <summary>
        /// Gets the connection string of the SAC db.
        /// </summary>
        public string ConpareIncomingConnectionString
        {
#if local
            get { return "Server=srv.soitic.com;Database=CONPARE;Uid=sa;Pwd=medsys2o12;"; }
#else
            get { return "Server=200.199.34.58,1999;Database=CONPARE;Uid=sa;Pwd=medsys2o12;"; } // essa eh do outcoming
#endif
            //200.199.34.58,1999
            //get { return "Server=200.199.34.58,1999;Database=CONPARE;Uid=sa;Pwd=medsys2o12;"; } // essa eh do outcoming
            //get { return "Server=187.49.197.78,1999;Database=CONPARE;Uid=sa;Pwd=medsys2o12;"; } // essa eh do outcoming
            //get { return "Server=187.49.197.78,1999;Database=SAC;Uid=sa;Pwd=medsys2o12;"; } // correto
        }

        /// <summary>
        /// Gets the connection string of the SAC db.
        /// </summary>
        public string ConpareOutcomingConnectionString
        {
#if local
            get { return "Server=srv.soitic.com;Database=CONPARE;Uid=sa;Pwd=medsys2o12;"; }
#else
            get { return "Server=200.199.34.58,1999;Database=CONPARE;Uid=sa;Pwd=medsys2o12;"; }
#endif
            //200.199.34.58,1999
            //get { return "Server=200.199.34.58,1999;Database=CONPARE;Uid=sa;Pwd=medsys2o12;"; }
            //get { return "Server=srv.soitic.com,1433;Database=CONPARE;Uid=sa;Pwd=medsys2o12;"; }
            //get { return "Server=187.49.197.78,1999;Database=CONPARE;Uid=sa;Pwd=medsys2o12;"; }
            //get { return "Server=srv\\SAC;Database=CONPARE;Uid=sa;Pwd=medsys2o12;"; }
        }

        /// <summary>
        /// Gets the connection string of the SAC db.
        /// </summary>
        public string BIConnectionString
        {
#if local
            get { return "Server=srv.soitic.com;Database=SAC;Uid=sa;Pwd=medsys2o12;"; }
#else
            get { return "Server=200.199.34.58,1999;Database=SAC;Uid=sa;Pwd=medsys2o12;"; }
#endif

            //200.199.34.58,1999
            //get { return "Server=200.199.34.58,1999;Database=SAC;Uid=sa;Pwd=medsys2o12;"; }
            //get { return "Server=187.49.197.78,1999;Database=SAC;Uid=sa;Pwd=medsys2o12;"; }
        }

        /// <summary>
        /// Gets or sets the ampliart connection string.
        /// </summary>
        public string AmpliartConnectionString
        {
            get { return "Server=mysql.ampliart.com;Database=ampliart;Uid=ampliart;Pwd=ampli153;"; }
        }

        /// <summary>
        /// Gets the connection string of the SAC db.
        /// </summary>
        public string ForumConnectionString
        {
#if local
            get { return "Server=srv.soitic.com;Database=testes_med;Uid=sa;Pwd=medsys2o12;"; }
#else
            get { return "Server=200.199.34.58,1999;Database=testes_med;Uid=sa;Pwd=medsys2o12;"; }
#endif

            //200.199.34.58,1999
            //get { return "Server=200.199.34.58,1999;Database=testes_med;Uid=sa;Pwd=medsys2o12;"; }
            //get { return "Server=187.49.197.78,1999;Database=testes_med;Uid=sa;Pwd=medsys2o12;"; }
            //get { return "Server=SERVER,1433;Database=testes_med;Uid=sa;Pwd=medsys2o12;"; }

            // backup
            //get { return "Server=187.49.197.77,1999;Database=testes_med;Uid=sa;Pwd=medsys;"; }
            //get { return "Server=192.168.0.20,1433;Database=testes_med;Uid=sa;Pwd=medsys;"; }
            //get { return "Server=SERVIDOR\\MSSQLSERVER,1433;Database=testes_med;Uid=sa;Pwd=medsys;"; }
        }

        /// <summary>
        /// Gets the wiki connection string.
        /// </summary>
        public string WikiConnectionString
        {
            get { return "Server=srv.soitic.com;Database=wiki_soitic;Uid=root;Pwd=q1w2e3r4;"; }
        }

        public string ElfutecConnectionString
        {
            get { return "Server=mysql.elfutec.com.br;Database=elfutec;Uid=elfutec;Pwd=elf147;"; }
        }

        #endregion

        #region Properties

        public UserData UserData
        {
            get;
            set;
        }

        public static General Instance
        {
            get
            {
                return General.instance ?? (General.instance = new General());
            }
        }

        public string ConnectionString
        {
            get { return "Server=mysql.medapps.com.br;Database=medapps08;Uid=medapps08;Pwd=ppl153;"; }
            //get { return "Server=mysql.medapps.com.br;Database=medapps05;Uid=medapps05;Pwd=metra153;"; }
        }

        public string FreeTel1
        {
            get { return "0800 704 1133"; }
        }

        public string FreeTel2
        {
            get { return "0800 707 5900"; }
        }

        public string FixTel1
        {
            get { return "(35) 3729 9900"; }
        }

        public string CopyrightHTML
        {
            get { return "<span>&copy; Copyright 2014. Todos os direitos reservados. Soluções inteligentes <a target=\"_blank\" href=\"http://soitic.com\" title=\"Grupo SOITIC\">Grupo SOITIC</a></span>"; }
        }

        public string SupportEmail1
        {
            get { return "assistencia@soitic.com"; }
        }

        public string Favicon
        {
            get { return "<link rel=\"shortcut icon\" href=\"media/favicon.png\">"; }
        }

        #endregion

        #region Constructor

        public General()
        {
        }

        #endregion

        #region User Methods

        public static List<int> GetCodesToExclude()
        {
            List<int> codes = new List<int>();

            codes.Add(208);
            codes.Add(218);
            codes.Add(295);

            return codes;
        }

        public bool LoginUser(string login, string password, HttpSessionState session, out string message)
        {           
            this.LogoutUser(session);

            login = login.Replace("`", "");
            login = login.Replace("´", "");
            login = login.Replace("'", "");
            login = login.Replace(",", "");
            login = login.Replace("-", "");
            login = login.Replace("/", "");
            login = login.Replace("\\", "");

            password = password.Replace("`", "");
            password = password.Replace("´", "");
            password = password.Replace("'", "");
            password = password.Replace(".", "");
            password = password.Replace(",", "");
            password = password.Replace("-", "");
            password = password.Replace("/", "");
            password = password.Replace("\\", "");

            // Checking against common sql injection attacks
            message = "ok";
            if (General.Instance.IsAlphaNumericOnly(password) == false)
            {
                message = "Um dos campos possui um caractere inválido";
                return false;
            }

            if (General.Instance.HasInvalidWords(login) == true || General.Instance.HasInvalidWords(password) == true)
            {
                message = "Um dos campos possui palavras inválidas";
                return false;
            }

            PL.DataBase.DAO.Instance.Setup(PL.DataBase.DatabaseTypes.MySql, General.Instance.ConnectionString);
            PL.DataBase.DAO.Instance.Connector.Open();

            // 15326
            // 89501470172

            //string sql = "SELECT * FROM main WHERE Código=" + cod;
            string sql = "";
            sql += "SELECT * FROM tb_Usuarios WHERE Email='" + login + "' AND Senha='" + password + "' AND Excluido=0";

            var reader = PL.DataBase.DAO.Instance.Connector.QueryWithReader(sql);
            if (reader.Read() == true)
            {
                // Getting the user data
                this.UserData.IsValid = true;
                this.UserData.Type = (int)reader["Tipo"];
                this.UserData.ID = (int)reader["Id_Usuario"];
                if ((reader["Nome"] is DBNull) == false)
                {
                    this.UserData.Name = (string)reader["Nome"];
                }
                else
                {
                    this.UserData.Name = "-Sem nome cadastrado-";
                }
                if ((reader["Email"] is DBNull) == false)
                {
                    this.UserData.Email = (string)reader["Email"];
                }
                else
                {
                    this.UserData.Email = "-Sem email cadastrado-";
                }

                this.UserData.Address = "" + reader["Rua"] + " " + reader["Bairro"] + " " + reader["Cidade"] + " " + reader["UF"];
                if (string.IsNullOrEmpty(this.UserData.Address) == true)
                {
                    this.UserData.Address = "-Sem endereço cadastrado-";
                }

                if ((reader["DDDCelular"] is DBNull) == false)
                {
                    this.UserData.CelDDD = "" + reader["DDDCelular"];
                }
                else
                {
                    this.UserData.CelDDD = "";
                }

                if ((reader["Celular"] is DBNull) == false)
                {
                    this.UserData.Cel = (string)reader["Celular"];
                }
                else
                {
                    this.UserData.Cel = "-Sem celular cadastrado-";
                }

                if ((reader["DDDFixo"] is DBNull) == false)
                {
                    this.UserData.TelephoneDDD = "" + reader["DDDFixo"];
                }
                else
                {
                    this.UserData.TelephoneDDD = "";
                }

                if ((reader["Fixo"] is DBNull) == false)
                {
                    this.UserData.Telephone = (string)reader["Fixo"];
                }
                else
                {
                    this.UserData.Telephone = "-Sem telefone fixo cadastrado-";
                }

                session["Permissao_Fornecedores"] = "" + reader["Permissao_Fornecedores"];
                session["Permissao_ContasBancarias"] = "" + reader["Permissao_ContasBancarias"];
                session["Permissao_RelatoriosFinanceiros"] = "" + reader["Permissao_RelatoriosFinanceiros"];
                session["Permissao_RelatoriosFinanceirosComercial"] = "" + reader["Permissao_RelatoriosFinanceirosComercial"];
                session["Permissao_RelatoriosFinanceirosAmpliart"] = "" + reader["Permissao_RelatoriosFinanceirosAmpliart"];                
                session["Permissao_RelatoriosGerais"] = "" + reader["Permissao_RelatoriosGerais"];
                session["Permissao_RelatoriosElfutec"] = "" + reader["Permissao_RelatoriosElfutec"];
                session["Permissao_LogDeAcesso"] = "" + reader["Permissao_LogDeAcesso"];
                session["Tipo_Permissao_Vendas"] = "" + reader["Tipo_Permissao_Vendas"];
                reader.Close();                                                     
            }
            else
            {
                this.UserData.IsValid = false;
                reader.Close();
            }

            if (this.UserData.IsValid == false)
            {
                message = "O email e/ou senha estão errados. Tente novamente ou entre em contato conosco.";
                PL.DataBase.DAO.Instance.Connector.Close();
                return false;
            }
            else
            {
                // Lets log the login attempt
                try
                {
                    string datetimeString = PL.General.GetTodaysDate();
                    DateTime datetimeObj = DateTime.Parse(datetimeString);
                    string weekday = datetimeObj.ToString("dddd", new System.Globalization.CultureInfo("pt-BR"));

                    sql = "";
                    sql += "INSERT INTO tb_LoginLog (Id_Usuario, LoginDate, Ip, Weekday) VALUES ('" + this.UserData.ID + "', '" + datetimeString + "', '" + PL.General.Instance.GetIPAddress() + "', '" + weekday + "')";
                    PL.DataBase.DAO.Instance.Connector.Execute(sql);
                }
                catch { }
            }

            PL.DataBase.DAO.Instance.Connector.Close();

            // User is valid, then we write its information to the session
            return this.WriteUserToSession(session);
        }

        /// <summary>
        /// Writes the user data to the session.
        /// </summary>
        /// <param name="session"></param>
        /// <returns></returns>
        public bool WriteUserToSession(HttpSessionState session)
        {
            session["UserData-Type"] = this.UserData.Type;
            session["UserData-IsValid"] = this.UserData.IsValid;
            session["UserData-ID"] = this.UserData.ID;
            session["UserData-Cel"] = this.UserData.Cel;
            session["UserData-DDD"] = this.UserData.CelDDD;
            session["UserData-Telephone"] = this.UserData.Telephone;
            session["UserData-TelephoneDDD"] = this.UserData.TelephoneDDD;     

            session["UserData-Name"] = this.UserData.Name;
            session["UserData-Email"] = this.UserData.Email;

            return true;
        }

        public void LogoutUser(HttpSessionState session)
        {
            session.Clear();
            this.UserData = new UserData();
            this.UserData.IsValid = false;
        }

        public bool IsSessionValid(HttpSessionState session)
        {
            if (session["UserData-ID"] == null)
            {
                return false;
            }

            try
            {
                int id = (int)session["UserData-ID"];
                if (id > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch
            {
                return false;
            }
        }

        #endregion

        #region Methods

        public string GetPaymentMethodLabel(string method)
        {
            string buffer = "";
            /*
                0 Dinheiro
                1 Cheque
                2 Cartão de Crédito
                3 Cartão de Débito
                4 Ticket
                5 Convênio
                6 Outra
                7 Boleto Bancário
                8 Vale Postal
                9 Débito Automático
                10 No Local
                11 Transferência Eletrônica
                12 Gerenciador Financeiro
                << Não preenchido >>
            */
            switch (method)
            {
                case "0":
                    buffer = "dinheiro";
                    break;
                case "1":
                    buffer = "Cheque";
                    break;
                case "2":
                    buffer = "Cartão de Crédito";
                    break;
                case "3":
                    buffer = "Cartão de Débito";
                    break;
                case "4":
                    buffer = "Ticket";
                    break;
                case "5":
                    buffer = "Convênio";
                    break;
                case "6":
                    buffer = "Outra";
                    break;
                case "7":
                    buffer = "Boleto Bancário";
                    break;
                case "8":
                    buffer = "Vale Postal";
                    break;
                case "9":
                    buffer = "Débito Automático";
                    break;
                case "10":
                    buffer = "No Local";
                    break;
                case "11":
                    buffer = "Transferência Eletrônica";
                    break;
                case "12":
                    buffer = "Gerenciador Financeiro";
                    break;
                default:
                    buffer = "<< Não preenchido >>";
                    break;


            }

            return buffer;
        }
        #endregion

        #region Helper Methods

        public static string GetTodaysDate()
        {
            return DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
        }

        public static string GetISOStringDate(DateTime dateTime)
        {
            return dateTime.ToString("yyyy-MM-dd HH:mm:ss");
        }

        public static string GetBRStringDate(DateTime dateTime)
        {
            return dateTime.ToString("dd/MM/yyyy HH:mm:ss");
        }

        public static DateTime GetTodaysDateInDatetime()
        {
            return DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
        }
        
        /// <summary>
        /// To get a human legible form of a formatted date.
        /// </summary>
        /// <param name="formattedDate"></param>
        /// <returns></returns>
        public static string GetLegibleDate(string formattedDate)
        {
            string legibleData = "";
            try
            {

                string[] buffer = formattedDate.Split(' ');
                string[] date = buffer[0].Split('-');
                string[] time = buffer[1].Split(':');

                legibleData = "" + date[2] + "/" + date[1] + "/" + date[0] + " às " + time[0] + ":" + time[1];
            }
            catch
            {
            }
            return legibleData;
        }

        public static string GetLegibleDate2(string formattedDate)
        {
            string legibleData = "";
            try
            {                
                string[] date = formattedDate.Split('-');
                legibleData = "" + date[2] + "/" + date[1] + "/" + date[0];
            }
            catch
            {
            }
            return legibleData;
        }

        public static string ToLegibleDate(string year, string month, string day)
        {
            if (year.Length == 1)
            {
                year = "0" + year;
            }
            if (month.Length == 1)
            {
                month = "0" + month;
            }
            if (day.Length == 1)
            {
                day = "0" + day;
            }

            return day + "/" + month + "/" + year;
        }

        protected string GetIPAddress()
        {
            System.Web.HttpContext context = System.Web.HttpContext.Current;
            string ipAddress = context.Request.ServerVariables["HTTP_X_FORWARDED_FOR"];

            if (!string.IsNullOrEmpty(ipAddress))
            {
                string[] addresses = ipAddress.Split(',');
                if (addresses.Length != 0)
                {
                    return addresses[0];
                }
            }

            return context.Request.ServerVariables["REMOTE_ADDR"];
        }

        public string ConvertToIso(string msg)
        {
            Encoding iso = Encoding.GetEncoding("ISO-8859-1");
            Encoding utf8 = Encoding.UTF8;
            byte[] utfBytes = utf8.GetBytes(msg);
            byte[] isoBytes = Encoding.Convert(utf8, iso, utfBytes);
            return iso.GetString(isoBytes);
        }

        public string ConvertToUTF8(string msg)
        {
            UTF8Encoding utf8 = new UTF8Encoding();
            string unicodeString = msg;
            byte[] encodedBytes = utf8.GetBytes(unicodeString);
            return utf8.GetString(encodedBytes);
        }

        public string GetEncodedText(string msg)
        {
            return HttpContext.Current.Server.HtmlEncode(msg);
        }

        public string GetAlert(string message)
        {
            return "<script>alert(\"" + message + "\")</script>";
        }

        public bool IsAlphaNumericOnly(string str)
        {
            Regex rg = new Regex("[^a-zA-Z0-9]");
            //if has non AlpahNumeric char, return false, else return true.
            return rg.IsMatch(str) == true ? false : true;
        }

        public bool HasInvalidWords(string str)
        {
            string textToTest = str.ToLower();

            List<string> invalidWords = new List<string>();
            // sql words
            invalidWords.Add("select");
            invalidWords.Add("from");
            invalidWords.Add("delete");
            invalidWords.Add("update");
            invalidWords.Add("drop");
            invalidWords.Add("join");
            invalidWords.Add("inner");
            // html words
            invalidWords.Add("form");

            foreach (string invalid in invalidWords)
            {
                if (Regex.IsMatch(textToTest, "\\b" + invalid + "\\b") == true)
                {
                    return true;
                }
            }

            return false;
        }

        public int MultiplyFactors(int n1, int n2)
        {
            int result = n1 * n2;
            return result;
        }

        #endregion
    }
}